import psycopg2
import psycopg2.extras
from shapely.wkt import dumps, loads
import constant
import sys


def updateMOD04():
	org_folder = "public/SatOrgMOD04"
	resample_folder= "apom/Resample/SatResampMOD04"
	mod04_query = "SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmod04"
	mod04_command = "UPDATE apom.satresampmod04 SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1,type=1,collection =051, gridid = 456081 WHERE filename = '{2}'"
	mod04type_command = "UPDATE apom.satresampmod04aerosoltype SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1,type=1,collection =051, gridid = 456081 WHERE filename = '{2}'"
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute(mod04_query)
	
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace(org_folder,resample_folder)
			corner = loads(row["corner"])

			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				mod04_filename = filename + "_DT_10km.tif"
				cur.execute(mod04_command.format(aqstime ,path, mod04_filename))
				mod04type_filename = filename + "_AT_10km.tif"
				cur.execute(mod04type_command.format(aqstime ,path, mod04type_filename))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print "Done MOD04"
def updateMYD04():
	org_folder = "public/SatOrgMYD04"
	resample_folder= "apom/Resample/SatResampMYD04"
	myd04_query = "SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmyd04"
	myd04_command = "UPDATE apom.satresampmyd04 SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1,type=1,collection =6, gridid = 456081 WHERE filename = '{2}'"
	myd04type_command = "UPDATE apom.satresampmyd04aerosoltype SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1,type=1,collection =6, gridid = 456081 WHERE filename = '{2}'"
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute(myd04_query)
	
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace(org_folder,resample_folder)
			corner = loads(row["corner"])

			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				mod_filename = filename + "_CB_10km.tif"
				cur.execute(myd04_command.format(aqstime ,path, mod_filename))
				modtype_filename = filename + "_AT_10km.tif"
				cur.execute(myd04type_command.format(aqstime ,path, modtype_filename))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print "Done MYD04"

def updateMYD07():
	con = None
	try:
		con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute("SELECT filename,path, aqstime, ST_AsText(corner) as corner FROM satorgmyd07")
		#cur.execute("SELECT filename, path, aqstime, ST_AsText(corner) as corner FROM satorgmyd07 where aqstime > '2014-06-12 00:00:00'::timestamp")
	
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			#if filename.endswith(".hdf"):	
			#	filename=filename.replace(".hdf","")
			
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace("public/SatOrgMYD07", "apom/Resample/SatResampMYD07")
			corner = loads(row["corner"])
			
			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				resamp_temperature = filename + "_T_10km.tif"
				resamp_pressure = filename + "_P_10km.tif"

				cur.execute("UPDATE apom.satresampmyd07temperature SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081, type = 1 WHERE filename = '{2}'".format(aqstime ,path, resamp_temperature))
				cur.execute("UPDATE apom.satresampmyd07pressure SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081, type = 1 WHERE filename = '{2}'".format(aqstime ,path, resamp_pressure))
				
				#cur.execute("UPDATE apom.satresampmod07pressure SET aqstime = TIMESTAMP '{0}', projection = 1, gridid = 456081 WHERE filename = '{1}'".format(aqstime , resamp_pressure))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print "Done MYD07"

def updateMOD07():
	con = None
	try:
		con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute("SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmod07")
		#cur.execute("SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmod07 where aqstime > '2014-03-18 00:00:00'::timestamp")
		
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace("public/SatOrgMOD07", "apom/Resample/SatResampMOD07")
			corner = loads(row["corner"])

			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				resamp_temperature = filename + "_T_10km.tif"
				resamp_pressure = filename + "_P_10km.tif"

				cur.execute("UPDATE apom.satresampmod07temperature SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_temperature))
				cur.execute("UPDATE apom.satresampmod07pressure SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_pressure))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print "Done MOD07"
def updateMOD07_presure():
	con = None
	try:
		con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute("SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmod07")
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace("public/SatOrgMOD07", "apom/Resample/SatResampMOD07")
			corner = loads(row["corner"])

			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				#resamp_temperature = filename + "_T_10km.tif"
				resamp_pressure = filename + "_P_10km.tif"

				#cur.execute("UPDATE apom.satresampmod07temperature SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_temperature))
				cur.execute("UPDATE apom.satresampmod07pressure SET aqstime = TIMESTAMP '{0}', filepath='{1}',projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_pressure))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print 'done'

def updateMYD07_presure():
	con = None
	try:
		con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute("SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmyd07")
		rows = cur.fetchall()
		
		cur.execute('BEGIN')
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			path = path.replace("public/SatOrgMYD07", "apom/Resample/SatResampMYD07")
			corner = loads(row["corner"])

			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				#resamp_temperature = filename + "_T_10km.tif"
				resamp_pressure = filename + "_P_10km.tif"

				#cur.execute("UPDATE apom.satresampmod07temperature SET aqstime = TIMESTAMP '{0}',filepath='{1}', projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_temperature))
				cur.execute("UPDATE apom.satresampmyd07pressure SET aqstime = TIMESTAMP '{0}', filepath='{1}',projection = 1, gridid = 456081 WHERE filename = '{2}'".format(aqstime ,path, resamp_pressure))
		cur.execute('COMMIT')

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	print 'done'
if __name__ == '__main__':
	
	#updateMOD04()
	updateMYD04()
	#updateMOD07()
	#updateMYD07()
	#updateMYD07_presure()